import java.awt.Color;
import java.io.FileOutputStream;  
import java.sql.Connection;  
import java.sql.DriverManager;  
import java.sql.ResultSet;  
import java.sql.SQLException;  
import java.sql.Statement;  
import java.util.ArrayList;  
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;  
import java.util.List;  
import java.util.Map;
import java.util.Set;

import com.lowagie.text.Cell;
import com.lowagie.text.Document;
import com.lowagie.text.Font;
import com.lowagie.text.PageSize;
import com.lowagie.text.Paragraph;
import com.lowagie.text.Table;
import com.lowagie.text.pdf.BaseFont;
import com.lowagie.text.rtf.RtfWriter2;
/**
 * 数据库文档生成器 Oracle版 
 *  
 */  
public class Oracle_DBDocer {  
    //键类型字典  
    private static Map<String,String> keyType = new HashMap<String,String>();  
    //初始化jdbc  
    static{  
        try {  
            keyType.put("P", "主键");  
//          keyType.put("C", "Check");  
            Class.forName("oracle.jdbc.OracleDriver");  
        } catch (ClassNotFoundException e) {  
            e.printStackTrace();  
        }  
    }  
    private static String url = "jdbc:oracle:thin:@localhost:1521:orcl";//链接url
    private static String username = "scott"; //用户名.需要设置默认表空间哈
    private static String password = "123456"; //密码
    private static String schema = "orcl"; //目标数据库名  
    //查询所有表的sql语句  
    private static String sql_get_all_tables = "select a.TABLE_NAME,b.COMMENTS from user_tables a,user_tab_comments b WHERE a.TABLE_NAME=b.TABLE_NAME order by TABLE_NAME"; //查询所有字段的sql语句  
    //查询要写入表的单元格的sql语句
    private static String sql_get_message="select   rownum 序号,\r\n" + 
    		"    A.column_name 列名, decode(e.key, A.column_name, '√', '') as 主键,\r\n" + 
    		"    A.data_type 类型,A.data_length 长度,\r\n" + 
    		"    case A.nullable   \r\n" + 
    		"    when 'Y' then '√'\r\n" + 
    		"    else '' end 允许空,A.data_default as 默认值,\r\n" + 
    		"    decode(i.key1,A.column_name,'√','') as 索引 \r\n" + 
    		"   ,B.comments 说明\r\n" + 
    		"from\r\n" + 
    		"    user_tab_columns A,\r\n" + 
    		"    user_col_comments B,\r\n" + 
    		"    (select count(*) IndexCount,Column_Name from User_Ind_Columns where Table_Name = '{table_name}' group by Column_Name ) C,\r\n" + 
    		"    (\r\n" + 
    		"select  a.column_name key\r\n" + 
    		"from user_cons_columns a, user_constraints b\r\n" + 
    		"where a.constraint_name = b.constraint_name\r\n" + 
    		"and b.constraint_type = 'P'\r\n" + 
    		"and a.table_name = '{table_name}'\r\n" + 
    		") e,\r\n" + 
    		"  (   SELECT a.COLUMN_NAME key1 FROM user_ind_columns a,User_Indexes b\r\n" + 
    		"   WHERE a.index_name=b.index_name AND b.UNIQUENESS LIKE 'UNIQUE%'\r\n" + 
    		"    AND a.table_name='{table_name}')i\r\n" + 
    		"where\r\n" + 
    		"    A.Table_Name = B.Table_Name\r\n" + 
    		"    and A.Column_Name = B.Column_Name\r\n" + 
    		"    and A.Column_Name = C.Column_Name(+)\r\n" + 
    		"    and A.Table_Name = '{table_name}'\r\n" + 
    		"    ";
    private static String sql_get_all_columns = "select T1.column_name,T1.data_type,T1.data_length,t2.comments,T1.NULLABLE,(select max(constraint_type)    from user_constraints x left join user_cons_columns y on x.constraint_name=y.constraint_name where x.table_name=t1.TABLE_NAME and y.COLUMN_NAME=T1.column_name)  from user_tab_cols t1, user_col_comments t2, user_tab_comments t3  where t1.TABLE_NAME=t2.table_name(+)  and t1.COLUMN_NAME=t2.column_name(+)  and t1.TABLE_NAME=t3.table_name(+)  and t1.TABLE_NAME='{table_name}' order by T1.COLUMN_ID ";  
    
    public static void main(String[] args) throws Exception {  
        //初始化word文档  
        Document document = new Document(PageSize.A4);
        String url="F:/word.doc";
        RtfWriter2.getInstance(document,new FileOutputStream(url));
        document.open();    
        //查询开始  
        Connection conn = getConnection();  
        //获取所有表  
       List tables = getDataBySQL(sql_get_all_tables,conn);
      /*  List tables=new ArrayList<String>();
        tables.add("MENU_T");*/
      int i=1;  
        for (Iterator iterator = tables.iterator(); iterator.hasNext();) {  
            String [] arr = (String []) iterator.next();  
            //循环获取字段信息  
            System.out.print(i+".正在处理数据表-----------"+arr[0]);  
            addTableMetaData(document,arr,i);  
            List columns = getDataBySQL(sql_get_message.replace("{table_name}", arr[0]),conn);  
            addTableDetail(document,columns);  
            addBlank(document);  
            System.out.println("...done");  
            i++;  
        }
      /*  List columns = getDataBySQL(sql_get_message.replace("MENU_T", "MENU_T"),conn); 
     
        
       	addTableDetail(document,columns);  
        addBlank(document);  
        System.out.println("...done"); */
        
        document.close();    
        conn.close();  
    }  
    /** 
     * 添加一个空行 
     * @param document 
     * @throws Exception 
     */  
    public static void addBlank(Document document)throws Exception{  
        Paragraph ph = new Paragraph("");
        ph.setAlignment(Paragraph.ALIGN_LEFT);   
        document.add(ph);  
    }  
    /** 
     * 添加包含字段详细信息的表格 
     */  
    public static void addTableDetail(Document document,List columns)throws Exception{  
    	Color PaleTurquoise1 = new Color(187 ,255, 255);
    	//本地宋体字体路径
    /*	String fontPath = "C:\\Windows\\Fonts\\simsun.ttc";
    	BaseFont songti = BaseFont.createFont(fontPath,BaseFont.IDENTITY_H, BaseFont.NOT_EMBEDDED);
    	Font font = new Font(songti, 32);
    	*/
        Table table = new Table(9);
        table.setWidth(100f);  
        table.setBorderWidth(1);    
        table.setBorderColor(Color.BLACK);
       
        table.setPadding(0);    
        table.setSpacing(0);    
        Cell cell1 = new Cell("序号");// 单元格
        cell1.setHeader(true);
       
        Cell cell2 = new Cell("列名");// 单元格  
        cell2.setHeader(true);   
          
        Cell cell3 = new Cell("主键");// 单元格  
        cell3.setHeader(true);   
         
        Cell cell4 = new Cell("类型");// 单元格  
        cell4.setHeader(true);   
          
        Cell cell5 = new Cell("长度");// 单元格  
        cell5.setHeader(true);   
          
        Cell cell6 = new Cell("允许空");// 单元格 
        cell6.setHeader(true);  
        Cell  cell7=new Cell("默认值");//单元格
        cell7.setHeader(true);
        Cell cell8=new Cell("索引");//单元格
        cell8.setHeader(true);
        Cell cell9=new Cell("说明");//单元格
        cell9.setHeader(true);
        //设置表头格式  
        table.setWidths(new float[]{8f,20f,8f,15f,8f,10f,10f,10f,29f}); 
        //序号居中 
        cell1.setHorizontalAlignment(Cell.ALIGN_CENTER);
        cell1.setBackgroundColor(PaleTurquoise1);  
      
        //列名
        cell2.setHorizontalAlignment(Cell.ALIGN_CENTER);  
        cell2.setBackgroundColor(PaleTurquoise1);  
        //主键
        cell3.setHorizontalAlignment(Cell.ALIGN_CENTER);  
        cell3.setBackgroundColor(PaleTurquoise1); 
        //类型
        cell4.setHorizontalAlignment(Cell.ALIGN_CENTER);  
        cell4.setBackgroundColor(PaleTurquoise1);  
        //长度
        cell5.setHorizontalAlignment(Cell.ALIGN_CENTER);  
        cell5.setBackgroundColor(PaleTurquoise1);  
        //允许空
        cell6.setHorizontalAlignment(Cell.ALIGN_CENTER);  
        cell6.setBackgroundColor(PaleTurquoise1);  
        //默认值
        cell7.setHorizontalAlignment(Cell.ALIGN_CENTER);  
        cell7.setBackgroundColor(PaleTurquoise1);
        //索引
        cell8.setHorizontalAlignment(Cell.ALIGN_CENTER); 
        cell8.setBackgroundColor(PaleTurquoise1);
        //说明
        cell9.setHorizontalAlignment(Cell.ALIGN_CENTER); 
        cell9.setBackgroundColor(PaleTurquoise1);      
       
        table.addCell(cell1);    
        table.addCell(cell2);    
        table.addCell(cell3);    
        table.addCell(cell4);    
        table.addCell(cell5);  
        table.addCell(cell6);  
        table.addCell(cell7);  
        table.addCell(cell8);  
        table.addCell(cell9);  
        table.endHeaders();// 表头结束  
        
        int x = 1;  
      
        for (Iterator iterator = columns.iterator(); iterator.hasNext();) {  
        	String [] arr2 = (String []) iterator.next();         
        	//序列号
            Cell c1 = new Cell(arr2[0]);  
            //列名
           
            Cell c2 = new Cell(arr2[1]);  
            //主键
            Cell c3 = new Cell(arr2[2]);  
            //类型
            Cell c4 = new Cell(arr2[3]);  
            //长度
            Cell c5=new Cell(arr2[4]);
            //允许空
            Cell c6=new Cell(arr2[5]);
            //默认值
            Cell c7=new Cell(arr2[6]);
            //索引
            Cell c8=new Cell(arr2[7]);
           
            //说明
            Cell c9=new Cell(arr2[8]);
           
            c1.setHorizontalAlignment(Cell.ALIGN_CENTER);  
            c2.setHorizontalAlignment(Cell.ALIGN_CENTER);  
            c3.setHorizontalAlignment(Cell.ALIGN_CENTER);  
            c4.setHorizontalAlignment(Cell.ALIGN_CENTER);  
            c5.setHorizontalAlignment(Cell.ALIGN_CENTER);  
            c6.setHorizontalAlignment(Cell.ALIGN_CENTER);  
            c7.setHorizontalAlignment(Cell.ALIGN_CENTER);  
            c8.setHorizontalAlignment(Cell.ALIGN_CENTER);  
            c9.setHorizontalAlignment(Cell.ALIGN_CENTER);  
            table.addCell(c1);  
            table.addCell(c2);  
            table.addCell(c3);  
            table.addCell(c4);  
            table.addCell(c5);  
            table.addCell(c6);  
            table.addCell(c7);  
            table.addCell(c8);  
            table.addCell(c9);  
            x++;  
        } 
       
        document.add(table);  
        
    }  
    /** 
     * 增加表概要信息 
     */  
    public static void addTableMetaData(Document dcument,String [] arr,int i) throws Exception{  
        Paragraph ph = new Paragraph(i+". 表名: "+arr[0]+"("+(arr[1]==null?"":arr[1])+")");  
        ph.setAlignment(Paragraph.ALIGN_CENTER);   
        dcument.add(ph);  
    }  
    /** 
     * 把SQL语句查询出列表 
     */  
    public static List getDataBySQL(String sql,Connection conn){  
        Statement stmt = null;  
        ResultSet rs = null;  
        List list = new ArrayList();  
        try {  
            stmt = conn.createStatement();  
            rs = stmt.executeQuery(sql);  
            while(rs.next()){  
                String [] arr = new String[rs.getMetaData().getColumnCount()];  
                for(int i=0;i<arr.length;i++){  
                    arr[i] = rs.getString(i+1);  
                }  
                list.add(arr);  
            }  
        } catch (SQLException e) {  
            e.printStackTrace();  
        }finally{  
            try {  
                if(rs!=null)rs.close();  
                if(stmt!=null)stmt.close();  
            } catch (SQLException e) {  
                e.printStackTrace();  
            }  
        }  
        return list;  
    }  
    /** 
     * 获取数据库连接 
     * @return 
     */  
    public static Connection getConnection(){  
        try {  
            return DriverManager.getConnection(url, username, password);  
        } catch (SQLException e) {  
            e.printStackTrace();  
        }  
        return null;  
    }  
    
    
   
}  